/**
 * @description excel批量合成过滤工具
 * @author aa055
 */


// 公共库
const xlsx = require('node-xlsx')
const fs = require('fs')
// excel文件夹路径（把要合并的文件放在excel文件夹内）
const _file = `${__dirname}/excel/`
const _output = `${__dirname}/result/`

// 表头
let headerIndex = 1
// 统计合并的文件数
let count = 0
// 启用excel写入 小于等于100表示多少个文件合并为一个，大于100表示多少条数据合并为一个文件
let flag = 1
// 合并数据的结果集
let dataList = [{
  name: 'sheet1',
  data: []
}]
let result = []

const fontFamily =
  'font-family: Poppins, PingFang SC, ui-sans-serif, system-ui, -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Noto Color Emoji";';

function unique (arr) {
  return Array.from(new Set(arr))
}
init()

const deleteRow = [];//require('./remove.json')
function init () {
  fs.readdir(_file, function (err, files) {
    console.log("📂", files)
    if (err) {
      throw err
    }
    // files是一个数组
    // 每个元素是此目录下的文件或文件夹的名称
    files.forEach((item, index) => {
      try {
        if (item === '.DS_Store') {
          return
        }
        // 过滤掉临时文件
        if (item.indexOf('~$') === -1) {
          console.log(`🐬开始合并与过滤：${item}`)
          console.time()
          let excelData = xlsx.parse(`${_file}${item}`)
          /*
          [
            {
              name: 'Sheet1', // 工作表名称
              data: [ // 工作表数据
                [

                ],
                ...
              ]
            }
          ]
          */
          let sheet1 = excelData[0];
          let sheet1Headers = sheet1.data[headerIndex];
          let tableData = sheet1.data.slice(headerIndex + 1) // 去除表头
          console.log('🍀', '表头列数' + sheet1Headers.length)
          console.log('🍀', '数据行数' + tableData.length)
          let totalLength = tableData.length
          if (excelData) {
            let filterCount = 0;
            // !过滤
            for (let i = tableData.length - 1; i >= 0; i--) {
              /*
                [ '0', '原文件导入名称' ],
                [ '1', '系统匹配企业名称' ],
                [ '2', '登记状态' ],
                [ '3', '统一社会信用代码' ],
                [ '4', '法定代表人' ],
                [ '5', '成立日期' ],
                [ '6', '核准日期' ],
                [ '7', '注册资本' ],
                [ '8', '实缴资本' ],
                [ '9', '电话' ],
                [ '10', '更多电话' ],
                [ '11', '所属省份' ],
                [ '12', '所属城市' ],
                [ '13', '所属区县' ],
                [ '14', '企业地址' ],
                [ '15', '通信地址' ],
                [ '16', '邮箱' ],
                [ '17', '更多邮箱' ],
                [ '18', '登记机关' ],
                [ '19', '企业(机构)类型' ],
                [ '20', '纳税人资质' ],
                [ '21', '纳税人识别号' ],
                [ '22', '注册号' ],
                [ '23', '组织机构代码' ],
                [ '24', '营业期限' ],
                [ '25', '国标行业门类' ],
                [ '26', '国标行业大类' ],
                [ '27', '国标行业中类' ],
                [ '28', '国标行业小类' ],
                [ '29', '曾用名' ],
                [ '30', '英文名' ],
                [ '31', '企业规模' ],
                [ '32', '经营范围' ],
                [ '33', '企业简介' ],
                [ '34', '官网' ],
                [ '35', '最新年报年份' ],
                [ '36', '参保人数' ],
                [ '37', '参保人数所属年报' ]
              */
              let row = tableData[i]
              let originText = row[0]// 原文件导入名称
              let status = row[2]// 登记状态
              let code = row[3]// 统一社会信用代码
              let type = row[19]// 企业(机构)类型
              let year = row[35]// 最新年报年份


              if (!(status == "存续" || status == "在业")) {
                // console.log("status", status);
                filterCount++
                tableData.splice(i, 1)
              } else if (!type.includes('个体') || type.includes('台湾') || type.includes('香港') || type.includes('澳门')) {
                // console.log("type",  type);
                filterCount++
                tableData.splice(i, 1)
              } else if (year == 2024) {
                // console.log("year", year);
                filterCount++
                tableData.splice(i, 1)
              } else if (originText !== code && !/[\u4e00-\u9fa5]/.test(originText)) {
                // console.log("code", code, originText);
                filterCount++
                tableData.splice(i, 1)
              } else if (deleteRow[item] && deleteRow[item].indexOf(originText) > -1) {
                filterCount++
                tableData.splice(i, 1)
              }

              // 写入excel
              if (flag > 100 && dataList[0].data.length >= flag) {
                writeExcel(dataList.splice, `天眼查_${count / flag}`)
                dataList = [{
                  name: 'sheet1',
                  data: []
                }]
                dataList[0].data = [sheet1Headers]
              }


              if (i == 0) {
                console.log(`%c🔨${item}过滤数量为：` + filterCount + '条', `color: #67C23A; font-size: 1rem;${fontFamily}`)
                result.push({
                  name: item,
                  filterCount,
                  total: totalLength
                })
              }
            }

            if (dataList[0].data.length == 0) {
              dataList[0].data.push(sheet1Headers)
            }
            // !进行合并操作
            dataList[0].data = dataList[0].data.concat(tableData)
            // console.log(dataList[0].data.length);
          }
          count++
          console.timeEnd()
        }
      } catch (e) {
        console.log(e)
        console.log('❗excel表格内部字段不一致，请检查后再合并。❗')
      }

      // 写入excel
      if (flag <= 100 && (count % flag === 0 || (files.length <= flag && count === files.length))) {
        writeExcel(dataList, `天眼查_${Math.ceil(count / flag)}`)
        dataList = [{
          name: 'sheet1',
          data: []
        }]
        dataList[0].data = []
      }
    })

    let { total, filterCount } = result.reduce((pre, cur) => ({ total: pre.total + cur.total, filterCount: pre.filterCount + cur.filterCount }), { total: 0, filterCount: 0 })
    console.log('🔧 %c过滤后所有数据数量为：' + (total - filterCount) + '条 ', `color: #007bff; font-size: 1rem;${fontFamily}`)
    console.log('🔧 %c过滤的数量为:' + filterCount + '条 ', `color: #007bff; font-size: 1rem;${fontFamily}`)
    console.log('🔧 %c成功合并数量为:' + count + '个表格', `color: #007bff; font-size: 1rem;${fontFamily}`)
    console.log('🔧 %c' + JSON.stringify(result), `color: #007bff; font-size: 1rem;${fontFamily}`)
    // 写xlsx
    // let componyArr = []
    // tableData.forEach(item => {
    // 	componyArr.push(item[1])
    // })

    /*
    for (let i = 0; i < tableData.length; i++) {
      let maxIndex = 0
      let maxLen = 0
      if (tableData[i]) {
        maxLen = tableData[i].length
        for (let j = i + 1; j < tableData.length; j++) {
          if (tableData[j] && tableData[i]) {
            if (tableData[i][1] === tableData[j][1]) {
              let maxOri = true // true为 i比较   false j比较
              let iLen = tableData[i].filter(item => item).length
              let jLen = tableData[j].filter(item => item).length
              // console.log(i,j)
              if (maxOri) {
                if (iLen >= jLen) {
                  maxIndex = i
                  maxLen = iLen
                  tableData.splice(j, 1, null)
                } else {
                  maxIndex = j
                  maxOri = false
                  maxLen = jLen
                  tableData.splice(i, 1, null)
                }
              } else {
                if (maxLen < jLen) {
                  tableData.splice(maxIndex, 1, null)
                  maxIndex = j
                  maxLen = jLen
                } else {
                  tableData.splice(j, 1, null)
                }
              }
            }
          }
        }
      } else {
        maxIndex = 0
      }
    }
    */
    // const data = unique(componyArr)
  })
}
const XLSX = require('xlsx');
function writeExcel (data, fileName) {
  /*
  console.time();
  console.log("🍬开始写入");

  var buffer = xlsx.build(data)
  fs.writeFile(`${_output}${fileName}.xlsx`, buffer, function (err) {
    if (err) {
      throw err
    }
  })
  console.log("🍬写入完成");
  console.timeEnd()
  */


  console.time()
  let newlist = data[0].data.slice(1).map((item, index) => {
    let temp = {}
    data[0].data[0].forEach((item2, index2) => { temp[item2] = item[index2] })
    return temp
  })
  console.log(`🍬开始写入：${_output}${fileName}.xlsx`, newlist.length);

  // console.log(JSON.stringify(newlist[0], null, 2));
  const ws = XLSX.utils.json_to_sheet(newlist);
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  // 生成Excel文件
  XLSX.writeFile(wb, `${_output}${fileName}.xlsx`, {
    compression: true,  // ✔️ 关键压缩选项
    bookSST: true,      // 启用共享字符串表
    bookType: 'xlsx'    // 指定输出格式
  });
  console.log(`👌完成合并：${_output}${fileName}.xlsx`)
  console.timeEnd()

}




/*
console.time()
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('sheet1');
data[0].data.forEach((item, index) => {
  worksheet.addRow(item);
})
console.log("🍬写入中...");
workbook.xlsx.writeFile(`${_output}${fileName}.xlsx`, {
  compression: true  // 默认启用高效压缩
});
console.timeEnd()
console.log("🍬写入完成");
*/
// 转换为Excel
/*
console.time()
let newlist = data[0].data.map((item, index) => {
  let temp = {}
  item.forEach((item2, index2) => { temp[data[0].data[headerIndex][index2]] = item2 })
  return temp
})
newlist.splice(0, 1)
const ws = XLSX.utils.json_to_sheet(newlist);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

// 生成Excel文件
XLSX.writeFile(wb, `${_output}${fileName}.xlsx`, {
  compression: true,  // ✔️ 关键压缩选项
  bookSST: true,      // 启用共享字符串表
  bookType: 'xlsx'    // 指定输出格式
});
console.log(`👌完成合并：${_output}${fileName}.xlsx`)
console.timeEnd()
*/


/*
//! 使用zlib模块压缩
//! Node.js内置的zlib模块提供多种压缩算法：
const sheetOptions = { '!cols': [{ wch: 6 }, { wch: 7 }, { wch: 10 }, { wch: 20 }] };
var buffer = xlsx.build(data, sheetOptions)
const zlib = require('zlib');
// const input = Buffer.from('待压缩数据');
// console.log(`👌完成合并：${_output}${fileName}.xlsx`)

//? Gzip压缩（高压缩比）
zlib.gzip(buffer, (err, compressed) => {
  if (!err) {
    console.log('压缩后:', compressed.length);

    fs.writeFile(`${_output}${fileName}.gz`, compressed, function (err) {
      if (err) {
        throw err
      }
      console.log('压缩率:', (compressed.length / buffer.length * 100).toFixed(1) + '%')
    })
  }
});

//? Deflate压缩（更快速度）
// zlib.deflate(input, (err, compressed) => {
//   if (!err) console.log('压缩后:', compressed.length);
// });
*/

/*
//! 流式处理大Buffer‌
//! 通过管道连接流实现内存优化：
const gzip = zlib.createGzip();

fs.createReadStream('input.txt')
.pipe(gzip)
.pipe(fs.createWriteStream('output.gz'));
*/




/*
let excelData = xlsx.parse(`${_file}${item}`)
let sheet1 = excelData[0];
let tableData = sheet1.data.slice(headerIndex + 1)
for (let i = tableData.length - 1; i >= 0; i--) {
let row = tableData[i]
if (setList[row[0]]) {
  setList[row[0]] = { item, code: row[0] }
  deleteRow[item] ? deleteRow[item].push(row[0]) : (deleteRow[item] = [row[0]])
} else {
  setList[row[0]] = "-"
}
}
console.log(item);
console.log(Object.keys(setList).length);
Object.keys(deleteRow).forEach(item => console.log(deleteRow[item].length))

fs.writeFile(`a.json`, JSON.stringify(deleteRow), function (err) {
if (err) {
  throw err
}
})
*/